package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.bean.ViewScoped;
import javax.sql.DataSource;

import fact.view.DTO.ScenarioDTO;
import fact.view.DTO.SegmentDTO;

@ManagedBean(name = "scenarioModelBean")
//@RequestScoped
@ViewScoped
public class ScenarioModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<ScenarioDTO> getScenarioList() throws SQLException
	{
		ArrayList<ScenarioDTO> scenarioList=new ArrayList<ScenarioDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SCENARIO Order by POSITION_NO,SCENARIO_NAME");  
		ResultSet result =  ps.executeQuery();
		ScenarioDTO s=null;
				while(result.next())
				{
					s=new ScenarioDTO();
					s.setScenarioId(result.getInt("SCENARIO_ID"));
					s.setScenarioName(result.getString("SCENARIO_NAME"));
					s.setPosition(result.getInt("POSITION_NO"));
					
					scenarioList.add(s);
				}
		
		
		return scenarioList;
	}
	
	public ScenarioDTO getScenario(String scenarioName) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SCENARIO Where upper(SCENARIO_NAME)=upper(?)");  
		ps.setString(1, scenarioName);
		ResultSet result =  ps.executeQuery();
		ScenarioDTO s=null;
				if(result.next())
				{
					s=new ScenarioDTO();
					s.setScenarioId(result.getInt("SCENARIO_ID"));
					s.setScenarioName(result.getString("SCENARIO_NAME"));
					s.setPosition(result.getInt("POSITION_NO"));
				}
		
		return s;
	}
	
	public ScenarioDTO getScenario(int scenarioId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select * from MST_SCENARIO Where SCENARIO_ID=?");  
		ps.setInt(1, scenarioId);
		ResultSet result =  ps.executeQuery();
		ScenarioDTO s=null;
				if(result.next())
				{
					s=new ScenarioDTO();
					s.setScenarioId(result.getInt("SCENARIO_ID"));
					s.setScenarioName(result.getString("SCENARIO_NAME"));
					s.setPosition(result.getInt("POSITION_NO"));
				}
		
		
		return s;
	}
	
	
	public boolean addScenario(String scenarioName,int position) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Insert into MST_SCENARIO Values(SQN_SCENARIO.NEXTVAL,?,?)");  
		ps.setString(1, scenarioName);
		ps.setInt(2, position);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean deleteScenario(int scenarioId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete MST_SCENARIO Where SCENARIO_ID=?");  
		ps.setInt(1, scenarioId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	

	public boolean editScenario(String scenarioName,int scenarioPosition,int scenarioId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update MST_SCENARIO Set SCENARIO_NAME=?,POSITION_NO=? Where SCENARIO_ID=?");  
		ps.setString(1, scenarioName);
		ps.setInt(2, scenarioPosition);
		ps.setInt(3, scenarioId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
		

}